package SQLManager;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import MTProxyServer.MTConstant;


public class SQLManager {

	private String mHost = "jdbc:mysql://" + MTConstant.MT_SQL_HOST + "/";
    
	private Connection mConnection = null;
    private Statement mStatement = null;
   
    
    private static SQLManager mSQLManager = null; 
    
    public SQLManager(){
    	try{
    		Class.forName("com.mysql.jdbc.Driver").newInstance();
    		
    		ConnectSQL( );
    		
    		// Check whether the proxyserver is exist
    		String query = "SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '" + MTConstant.MT_SQL_DB + "'";
    		ResultSet isExist = Query( query, "" );
    		
    		isExist.last();
    		
    		if( 0 == isExist.getRow() ){
    			System.out.println( "Create a proxy server database." );
        		
        		// Create a proxy server database
    			// Create A new User DB
				query = "CREATE SCHEMA " + MTConstant.MT_SQL_DB;
				UpdateTable( query );
				
				// create event table
				query = "CREATE  TABLE " + MTConstant.MT_SQL_DB + ".EventTable (" +
						  "EventID INT NOT NULL AUTO_INCREMENT ," +
						  "Master INT NULL DEFAULT NULL ," +
						  "Title VARCHAR(45) NULL DEFAULT NULL , "+
						  "ContactMember VARCHAR(45) NULL DEFAULT NULL ," +
						  "Location VARCHAR(45) NULL DEFAULT NULL ,"+
						  "IsTerminated TINYINT(1)  NULL DEFAULT '0' ," +
						  "PRIMARY KEY (EventID) ," +
						  "UNIQUE INDEX EventID_UNIQUE (EventID ASC) ) ";
				UpdateTable( query );
				
				query = "CREATE  TABLE " + MTConstant.MT_SQL_DB + ".UserTable (" +
				  "UserID INT NOT NULL AUTO_INCREMENT ," +
				  "UserName VARCHAR(45) NULL DEFAULT NULL ," +
				  "PhoneNumber VARCHAR(45) NULL DEFAULT NULL , "+
				  "IP VARCHAR(45) NULL DEFAULT NULL ," +
				  "PRIMARY KEY (UserID) ," +
				  "UNIQUE INDEX UserID_UNIQUE (UserID ASC) ) ";
				
        		// create usertable
				UpdateTable( query );
    		}
    		else{
    			System.out.println( "Proxy server database exist." );
        		
    		}
    		this.Close();
    		
    	}
    	catch( Exception e ){
    		e.printStackTrace();
    	}
    }
    
    public static SQLManager GetInstance()
    {
    	if( null == mSQLManager ){
    		mSQLManager = new SQLManager();
    	}
    	return mSQLManager;
    }
    
//    public void SetAccountInfo( String aUser, String aPassword )
//    {
//    	mUser = aUser;
//    	mPassword = aPassword;
//    }
    
    public void ConnectSQL( ) throws SQLException
    {
    	if( null == mConnection ){
    		mConnection = DriverManager.getConnection( mHost, MTConstant.MT_SQL_USERNAME, MTConstant.MT_SQL_USERPW );
    		mStatement = mConnection.createStatement();
    	}
    }
    
    public ResultSet Query( String aQuery, String database ) throws SQLException
    {
    	if( 0 != database.length() ){
    		String db = "USE "+ database;
    		mStatement.executeQuery( db );
    	}
    	return mStatement.executeQuery( aQuery );
    }
    
    public synchronized void UpdateTable( String aQuery ) throws SQLException
    {
    	mStatement.executeUpdate( aQuery );
    }
    
    public void Close() throws SQLException
    {
    	if( null != mStatement ){
    		mStatement.close();
    		mStatement = null;
    	}
    	
    	if( null != mConnection){
    		mConnection.close();
    		mConnection = null;
    	}
    }
    
}

